﻿using System.Data;
using System.Data.Common;
using IToursDataService;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace ToursDataService
{
    // 注意: 使用“重构”菜单上的“重命名”命令，可以同时更改代码、svc 和配置文件中的类名“Test”。
    public class UserInfoCtrl : IUserInfo
    {
        #region 获取系统用户及相关信息

        /// <summary>
        /// 根据UserName获取真实姓名
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public string GetNameByUserName(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT [Name] FROM aspnet_UserInfo WHERE UserName='{0}'", username);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        /// 通过用户真实姓名，获取EMail地址，由于前端的用户名与用户姓名的添加、修改控制了唯一性(真实姓名)，所以可以通过真实姓名获取EMail
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public string GetEMailAddressByName(string name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT EMail FROM aspnet_UserInfo WHERE Name='{0}'", name);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        /// <summary>
        /// 获取全部系统用户的'登录用户名'及'姓名'信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserInfoData()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = "SELECT [UserInfoID], [UserName], [Name] FROM aspnet_UserInfo ui,bas_Department dp where ui.DepartmentID = dp.DepartmentID and dp.DepartmentName <> '导游部'";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                DataRow dr = ds.Tables[0].NewRow();
                dr["Name"] = "";
                ds.Tables[0].Rows.Add(dr);
                return ds.Tables[0];
            }
            else
                return null;
        }
        #endregion
    }
}
